# 读取数据库
import pymysql


class MySQLConnect:
    conn = None
    # 游标
    cur = None

    def __init__(self) -> None:
        """
            在初始化时连接mysql
        """
        # 1. 连接数据库
        self.conn = pymysql.connect(
            host='localhost',
            user='root',
            password='******',
            db='my_test',
            charset='utf8',
            # autocommit=True,    # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
        )

    # 创建表
    def create(self, sql: str):
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql)
        finally:
            self.conn.close()

    def select(self, sql: str, params: tuple = ()) -> tuple:
        try:
            with self.conn.cursor() as cursor:
                # Read a single record
                amount = cursor.execute(sql, params)
                result = cursor.fetchall()
                return result
        finally:
            self.conn.close()

    def insert(self, sql: str, params: tuple):
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql, tuple)
                row = cursor.rowcount
            # connection is not autocommit by default. So you must commit to save
            # your changes.
            self.conn.commit()
            return row
        except Exception as e:
            print(e)
            self.conn.rollback()
        finally:
            self.conn.close()

    def insertBatch(self, sql: str, params: tuple):
        try:
            with self.conn.cursor() as cursor:
                cursor.executemany(sql, params)
                row = cursor.rowcount
            # connection is not autocommit by default. So you must commit to save
            # your changes.
            self.conn.commit()
            return row
        except Exception as e:
            print(e)
            self.conn.rollback()

        finally:
            self.conn.close()
